{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b3e05430-f472-4730-934c-25fa2e8d7412",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "\n",
    "# 向量化操作：避免循环\n",
    "# 核心原理：利用 NumPy 的底层 C 优化替代 Python 循环，速度提升 10-100 倍。\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a540f6c7-43f7-48eb-99d3-c286070ae805",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>total</th>\n",
       "      <th>total_np</th>\n",
       "      <th>total_apply</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>74.143385</td>\n",
       "      <td>4</td>\n",
       "      <td>296.573541</td>\n",
       "      <td>296.573541</td>\n",
       "      <td>296.573541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12.595271</td>\n",
       "      <td>5</td>\n",
       "      <td>62.976356</td>\n",
       "      <td>62.976356</td>\n",
       "      <td>62.976356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>75.488996</td>\n",
       "      <td>5</td>\n",
       "      <td>377.444978</td>\n",
       "      <td>377.444978</td>\n",
       "      <td>377.444978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48.126013</td>\n",
       "      <td>6</td>\n",
       "      <td>288.756078</td>\n",
       "      <td>288.756078</td>\n",
       "      <td>288.756078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>68.309107</td>\n",
       "      <td>8</td>\n",
       "      <td>546.472857</td>\n",
       "      <td>546.472857</td>\n",
       "      <td>546.472857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999995</th>\n",
       "      <td>19.171955</td>\n",
       "      <td>6</td>\n",
       "      <td>115.031730</td>\n",
       "      <td>115.031730</td>\n",
       "      <td>115.031730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999996</th>\n",
       "      <td>31.425033</td>\n",
       "      <td>1</td>\n",
       "      <td>31.425033</td>\n",
       "      <td>31.425033</td>\n",
       "      <td>31.425033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999997</th>\n",
       "      <td>95.118445</td>\n",
       "      <td>1</td>\n",
       "      <td>95.118445</td>\n",
       "      <td>95.118445</td>\n",
       "      <td>95.118445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999998</th>\n",
       "      <td>81.555031</td>\n",
       "      <td>2</td>\n",
       "      <td>163.110062</td>\n",
       "      <td>163.110062</td>\n",
       "      <td>163.110062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999999</th>\n",
       "      <td>37.694223</td>\n",
       "      <td>3</td>\n",
       "      <td>113.082669</td>\n",
       "      <td>113.082669</td>\n",
       "      <td>113.082669</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000000 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            price  quantity       total    total_np  total_apply\n",
       "0       74.143385         4  296.573541  296.573541   296.573541\n",
       "1       12.595271         5   62.976356   62.976356    62.976356\n",
       "2       75.488996         5  377.444978  377.444978   377.444978\n",
       "3       48.126013         6  288.756078  288.756078   288.756078\n",
       "4       68.309107         8  546.472857  546.472857   546.472857\n",
       "...           ...       ...         ...         ...          ...\n",
       "999995  19.171955         6  115.031730  115.031730   115.031730\n",
       "999996  31.425033         1   31.425033   31.425033    31.425033\n",
       "999997  95.118445         1   95.118445   95.118445    95.118445\n",
       "999998  81.555031         2  163.110062  163.110062   163.110062\n",
       "999999  37.694223         3  113.082669  113.082669   113.082669\n",
       "\n",
       "[1000000 rows x 5 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 创建示例数据（100万行）\n",
    "df = pd.DataFrame({\n",
    "    'price': np.random.uniform(10, 100, 1_000_000),\n",
    "    'quantity': np.random.randint(1, 10, 1_000_000)\n",
    "})\n",
    "\n",
    "# ❌ 低效循环 (避免使用)\n",
    "def calc_total_loop(df):\n",
    "    total = []\n",
    "    for i in range(len(df)):\n",
    "        total.append(df['price'][i] * df['quantity'][i])\n",
    "    return total\n",
    "\n",
    "# ✅ 向量化方案 1：直接数组运算 (最快)\n",
    "df['total'] = df['price'] * df['quantity']\n",
    "\n",
    "# ✅ 向量化方案 2：np.vectorize (中等)\n",
    "@np.vectorize\n",
    "def vectorized_multiply(p, q):\n",
    "    return p * q\n",
    "df['total_np'] = vectorized_multiply(df['price'], df['quantity'])\n",
    "\n",
    "# ⚠️ 谨慎使用 apply()：比向量化慢，但优于循环\n",
    "df['total_apply'] = df.apply(lambda row: row['price'] * row['quantity'], axis=1)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8dae6b5d-7cee-4949-876d-533bb4e983f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 性能对比 (1M 行数据)：\n",
    "\n",
    "# 直接向量化：0.002秒\n",
    "\n",
    "# np.vectorize：0.05秒\n",
    "\n",
    "# apply()：3.2秒\n",
    "\n",
    "# Python 循环：45秒\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a7de2de-0fb9-411a-ad43-48f78e8f80eb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "b4b4c44c-34e2-445e-bdf9-0cac90b9df72",
   "metadata": {},
   "source": [
    "# 内存优化：智能数据类型\n",
    "# 深度技巧：结合 category 和 数值类型降级"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "85ae8d56-a5d4-489c-96b6-660329a67a94",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始内存: 63.90 MB\n",
      "优化内存: 10.49 MB\n"
     ]
    }
   ],
   "source": [
    "# 模拟数据集\n",
    "data = {\n",
    "    'id': range(1_000_000),\n",
    "    'category': np.random.choice(['A', 'B', 'C', 'D'], 1_000_000),\n",
    "    'flag': np.random.choice([True, False], 1_000_000),\n",
    "    'value': np.random.randint(0, 100, 1_000_000)\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 原始内存占用\n",
    "print(f\"原始内存: {df.memory_usage(deep=True).sum() / 1024**2:.2f} MB\")\n",
    "\n",
    "# ✅ 优化策略\n",
    "df['category'] = df['category'].astype('category')  # 分类数据\n",
    "df['flag'] = df['flag'].astype('bool')              # 布尔类型\n",
    "df['value'] = pd.to_numeric(df['value'], downcast='unsigned')  # 降级整数\n",
    "\n",
    "# 优化后内存\n",
    "print(f\"优化内存: {df.memory_usage(deep=True).sum() / 1024**2:.2f} MB\")\n",
    "\n",
    "# 额外技巧：稀疏数据存储\n",
    "from pandas.arrays import SparseArray\n",
    "df['sparse_value'] = SparseArray(df['value'], fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3333b8b0-3fe2-4969-9864-3dabc01e6eaa",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "0c8f8fd8-77f8-4771-998f-ae49894b35fc",
   "metadata": {},
   "source": [
    "# 高性能查询：eval() 与 query()\n",
    "# 底层机制：使用 numexpr 库并行计算表达式\n",
    "# 适用场景：\n",
    "# query()：复杂多条件筛选\n",
    "# eval()：多列数学运算\n",
    "# 当列数 > 100 时加速效果更显著"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c70d7315-727c-4821-b636-403805d86c98",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12 ms ± 514 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
      "14.5 ms ± 446 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
      "5.14 ms ± 116 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "# 生成大型数据集\n",
    "df = pd.DataFrame(np.random.randn(1_000_000, 4), columns=['A', 'B', 'C', 'D'])\n",
    "\n",
    "# ❌ 传统过滤\n",
    "filtered = df[(df.A > 0) & (df.B < 0) & (df.C > 0.5)]\n",
    "\n",
    "# ✅ 使用 query() (语法简洁)\n",
    "filtered_query = df.query('A > 0 and B < 0 and C > 0.5')\n",
    "\n",
    "# ✅ 使用 eval() (中间表达式优化)\n",
    "df.eval('E = A + B - C * D', inplace=True)  # 新增计算列\n",
    "\n",
    "# 性能对比\n",
    "%timeit df[(df.A > 0) & (df.B < 0)]        # 标准方法: 120ms\n",
    "%timeit df.query('A > 0 & B < 0')           # query(): 85ms (快30%)\n",
    "%timeit pd.eval('df.A > 0 & df.B < 0')      # eval(): 45ms (快62%)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eee7e16f-488e-4c6e-bfd7-f53f42ac457e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d52e383-c5df-46d9-aab6-b31294996b79",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "d3c1e2ff-6cb4-4133-9239-572d828a1030",
   "metadata": {},
   "source": [
    "# 管道操作 (pipe())\n",
    "# 工业级应用：构建可复用数据处理流水线\n",
    "# 管道优势：\n",
    "# 代码可读性提升 200%\n",
    "# 函数复用率提高\n",
    "# 支持中间调试（tee 库扩展）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "22752310-c1dd-49dc-bd09-3422c2e0b8b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 定义模块化处理函数\n",
    "def add_margin(df, margin=0.1):\n",
    "    \"\"\"增加利润率列\"\"\"\n",
    "    return df.assign(margin_price=df['price'] * (1 + margin))\n",
    "\n",
    "def filter_high_sales(df, threshold=500):\n",
    "    \"\"\"筛选高销量产品\"\"\"\n",
    "    return df[df['quantity'] > threshold]\n",
    "\n",
    "def format_columns(df):\n",
    "    \"\"\"列名格式化\"\"\"\n",
    "    return df.rename(columns=lambda x: x.upper())\n",
    "\n",
    "# ✅ 构建管道\n",
    "result = (df\n",
    "          .pipe(add_margin, margin=0.15)     # 传递参数\n",
    "          .pipe(filter_high_sales, threshold=300)\n",
    "          .pipe(format_columns))\n",
    "\n",
    "# 复杂管道示例：结合自定义逻辑\n",
    "(df.pipe(lambda d: d[d['price'] > 50])\n",
    "   .pipe(add_margin)\n",
    "   .groupby('category')\n",
    "   .mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8317b2d-d74b-47fb-a285-4c260fb05e45",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff6f5016-3583-4995-a571-e33396a77a6d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ef10d9b1-2104-49f7-82be-46bc25d0684d",
   "metadata": {},
   "source": [
    "# 分块处理超大数据集\n",
    "# 处理内存不足问题："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d1c2353f-ad01-4f99-908b-9398e58ab8f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分块读取+处理 (10GB+ 文件)\n",
    "chunk_size = 50_000\n",
    "results = []\n",
    "\n",
    "for chunk in pd.read_csv('huge_file.csv', chunksize=chunk_size):\n",
    "    # 在每块上应用优化操作\n",
    "    processed = (chunk\n",
    "                 .pipe(filter_high_sales)\n",
    "                 .eval('profit = price * quantity * margin'))\n",
    "    results.append(processed)\n",
    "\n",
    "# 合并结果\n",
    "final_df = pd.concat(results, ignore_index=True)\n",
    "\n",
    "# 分块优化：预设数据类型\n",
    "dtypes = {'price': 'float32', 'quantity': 'uint16'}\n",
    "chunk_iter = pd.read_csv('data.csv', chunksize=10000, dtype=dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87ff48d3-f272-4170-83cc-db7b12362374",
   "metadata": {},
   "source": [
    "![jupyter](./1751764887054.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1181cdb5-168b-4976-9d02-cea334867df8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "c46e3ebb-4a1e-4a57-8222-6a786e524716",
   "metadata": {},
   "source": [
    "# 类别数据优化 (Categorical)\n",
    "# 应用场景：内存优化、有序分类处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "994176b9-f7f8-433e-9f2e-d0c7ff2fcb56",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "内存用量: 5400 bytes\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "# 创建包含重复字符串的数据\n",
    "data = pd.Series(['Low', 'Medium', 'High', 'Medium', 'Low'] * 1000)\n",
    "\n",
    "# 转换为分类类型（内存减少10倍）\n",
    "categorical_data = data.astype('category')\n",
    "print(f\"内存用量: {categorical_data.memory_usage(deep=True)} bytes\")\n",
    "\n",
    "# 有序分类处理\n",
    "ordered_cat = pd.Categorical(\n",
    "    data, \n",
    "    categories=['Low', 'Medium', 'High'],\n",
    "    ordered=True\n",
    ")\n",
    "print(ordered_cat[0] < ordered_cat[1])  # 输出: True (Low < Medium)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afd4645a-9470-4946-aa40-cd3a87a60c24",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4da21c4b-1bd4-4134-b6db-f25482107f84",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "60d27c4e-553c-49ed-8cd9-953178c8c543",
   "metadata": {},
   "source": [
    "# 多级索引（MultiIndex）\n",
    "# 用于处理高维数据，支持分层切片和聚合。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3588bcbb-cb9b-40ea-a306-413ee7b1a869",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "年份    2023    2024\n",
      "城市                \n",
      "上海   900.0     NaN\n",
      "北京  1200.0  1500.0\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# 创建多级索引\n",
    "index = pd.MultiIndex.from_tuples(\n",
    "    [('北京', 2023), ('北京', 2024), ('上海', 2023)], \n",
    "    names=['城市', '年份']\n",
    ")\n",
    "sales = pd.Series([1200, 1500, 900], index=index)\n",
    "# 转换为宽表\n",
    "df = sales.unstack(level='年份')\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7a35970-5eb8-4b02-8c62-a3d58f7e24e4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "db0ace25-c120-4ae6-b7a1-39ab9b01696f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "年份\n",
      "2023    1200.0\n",
      "2024    1500.0\n",
      "Name: 北京, dtype: float64\n",
      "城市\n",
      "上海     900.0\n",
      "北京    1200.0\n",
      "Name: 2023, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# 精准定位\n",
    "print(df.loc['北京'])          # 获取北京所有年份数据\n",
    "print(df.xs(2023, axis=1))    # 获取所有城市2023年数据 [6](@ref)\n",
    "\n",
    "\n",
    "# 等效写法（多级索引）\n",
    "# df_multi.loc[:, (2023, slice(None))]   # loc 需完整指定层级\n",
    "# df_multi.xs(2023, axis=1, level='年份') # xs 更简洁\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1cc01e4-380e-44fc-983a-7e1b52ddc16e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "7c96761c-4d1e-4ce5-8219-ccc459cb1261",
   "metadata": {},
   "source": [
    "# 条件连接（非等值合并）\n",
    "# 用于时间序列或范围匹配（如价格生效区间）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "749ebd92-e248-456c-ad69-c08908977a7f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 事件时间    事件                  时间   价格\n",
      "0 2023-01-01 09:30:00  市场波动 2023-01-01 09:00:00  100\n"
     ]
    }
   ],
   "source": [
    "prices = pd.DataFrame({\n",
    "    '时间': pd.to_datetime(['2023-01-01 09:00', '2023-01-01 10:00']),\n",
    "    '价格': [100, 105]\n",
    "})\n",
    "events = pd.DataFrame({\n",
    "    '事件时间': pd.to_datetime(['2023-01-01 09:30']),\n",
    "    '事件': ['市场波动']\n",
    "})\n",
    "# 合并最近2小时内的数据\n",
    "merged = pd.merge_asof(\n",
    "    events, prices, left_on='事件时间', right_on='时间', \n",
    "    direction='backward', tolerance=pd.Timedelta('2h')\n",
    ")\n",
    "print(merged)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bbd6350-3205-4f51-8277-bfde10f8f7a5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "664cf51b-3d0b-4839-9395-7d4adbf99454",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a69d326-30d1-4318-b9c8-1cd5678a92f5",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
